#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
股票信息表模型 (StockInfo)
"""

from sqlalchemy import Column, String, Integer, Float, DateTime, Index
from sqlalchemy.sql import func
from typing import List, Optional

from .BaseModel import BaseModel

class StockInfo(BaseModel):
    """股票信息表模型"""
    __tablename__ = 'stockinfo'
    
    # 列名映射（CSV列名 -> 数据库字段名）
    aliases = {
        'securityId': 'security_id',
        'securityName': 'security_name',
        'market': 'market',  
        'listDate': 'list_date',
        'delistDate': 'delist_date',
        'isActive': 'is_active',
        'publicfloatShare': 'public_float_share',
        '证券代码': 'security_id',
        '证券名称': 'security_name',
        '市场': 'market',
        '上市日期': 'list_date',
        '退市日期': 'delist_date',
        '是否活跃': 'is_active',
        '流通股本': 'public_float_share'
    }
    
    security_id = Column(String(20), primary_key=True)
    security_name = Column(String(100), nullable=False)
    market = Column(Integer, default=1, comment='市场：1-上海，2-深圳')
    public_float_share = Column(Integer, default=0, comment='流通股本')
    list_date = Column(String(10), comment='上市日期')
    delist_date = Column(String(10), comment='退市日期')
    is_active = Column(Integer, default=1, comment='是否活跃：1-活跃，0-退市')
    created_at = Column(DateTime, default=func.current_timestamp())
    updated_at = Column(DateTime, default=func.current_timestamp(), onupdate=func.current_timestamp())
    
    # 索引
    __table_args__ = (
        Index('idx_stock_info_market', 'market'),
        Index('idx_stock_info_is_active', 'is_active'),
    )
    
    @classmethod
    def get_session(cls):
        """获取数据库会话"""
        return cls._db_manager.get_stock_data_session()
    
    @classmethod
    def _get_existing_record(cls, session, data):
        """获取现有记录"""
        return session.query(cls).filter(cls.security_id == data['security_id']).first()
    
    @classmethod
    def _update_existing_record(cls, existing, data):
        """更新现有记录"""
        existing.security_name = data.get('security_name', existing.security_name)
        existing.market = data.get('market', existing.market)
        existing.public_float_share = data.get('public_float_share', existing.public_float_share)
        existing.list_date = data.get('list_date', existing.list_date)
        existing.delist_date = data.get('delist_date', existing.delist_date)
        existing.is_active = data.get('is_active', existing.is_active)
        existing.updated_at = func.current_timestamp()
    
    @classmethod
    def _to_dict(cls, result):
        """将结果转换为字典"""
        return {
            'security_id': result.security_id,
            'security_name': result.security_name,
            'market': result.market,
            'public_float_share': result.public_float_share,
            'list_date': result.list_date,
            'delist_date': result.delist_date,
            'is_active': result.is_active,
            'created_at': result.created_at,
            'updated_at': result.updated_at
        }
    
    @classmethod
    def get_all_stocks(cls) -> List[dict]:
        """获取所有股票信息（返回字典列表）"""
        if not cls._db_manager:
            raise ValueError("数据库管理器未设置，请先调用 set_db_manager()")
        
        try:
            session = cls.get_session()
            results = session.query(cls).all()
            session.close()
            return [cls._to_dict(result) for result in results]
        except Exception as e:
            # 获取所有股票失败: {str(e)}
            session.close()
            return []
    
    @classmethod
    def get_all_active_stocks(cls) -> List['StockInfo']:
        """获取所有活跃股票"""
        if not cls._db_manager:
            raise ValueError("数据库管理器未设置，请先调用 set_db_manager()")
        
        try:
            session = cls.get_session()
            results = session.query(cls).filter(cls.is_active == 1).all()
            session.close()
            return results
        except Exception as e:
            # 获取活跃股票失败: {str(e)}
            session.close()
            return []
    
   
    
    @classmethod
    def get_stock_name(cls, security_id: str) -> Optional[str]:
        """获取股票名称"""
        if not cls._db_manager:
            raise ValueError("数据库管理器未设置，请先调用 set_db_manager()")
        
        try:
            session = cls.get_session()
            stock = session.query(cls).filter(cls.security_id == security_id).first()
            session.close()
            return stock.security_name if stock else None
        except Exception as e:
            # 获取股票名称失败: {str(e)}
            session.close()
            return None
    
    @classmethod
    def import_from_dataframe(cls, df):
        """从DataFrame导入股票信息数据"""
        if not cls._db_manager:
            raise ValueError("数据库管理器未设置，请先调用 set_db_manager()")
        
        try:
            session = cls.get_session()
            success_count = 0
            
            for _, row in df.iterrows():
                try:
                    # 检查是否已存在相同记录
                    existing = session.query(cls).filter(
                        cls.security_id == row['security_id']
                    ).first()
                    
                    if existing:
                        # 更新现有记录
                        cls._update_existing_record(existing, row.to_dict())
                    else:
                        # 创建新记录
                        stock_info = cls(
                            security_id=row['security_id'],
                            security_name=row.get('security_name', ''),
                            market=row.get('market', 1),
                            public_float_share=row.get('public_float_share', 0),
                            list_date=row.get('list_date', ''),
                            delist_date=row.get('delist_date', ''),
                            is_active=row.get('is_active', 1)
                        )
                        session.add(stock_info)
                    
                    success_count += 1
                    
                except Exception as e:
                    # 处理股票信息数据失败: {str(e)}
                    continue
            
            session.commit()
            session.close()
            return success_count
            
        except Exception as e:
            # 导入股票信息数据失败: {str(e)}
            session.rollback()
            session.close()
            return 0
